using System;
using System.Data;
using System.Collections;
using APP.QUANLY.Entity;
using APP.QUANLY.Entity.ConditionForm;

namespace APP.QUANLY.DAL
{
    /// <summary>
    /// Summary description for NHANVIEN.
     /// </summary>
	public class NHANVIENDao
    {
	    private string _SQLSelectByKey;
	    private string _SQLUpdate;
	    private string _SQLInsert;
	    private string _SQLDelete;
	    private string _SQLSelectAll;

        #region Properties

        public IDbConnection _dbConnection;

        public IDbConnection DbConnection
        {
            get { return _dbConnection; }
            set { _dbConnection = value; }
        }

        #endregion
        
        #region Constructor

        public NHANVIENDao()
        {
            _dbConnection = null;
		    InitSqlQuery();
        }

        public NHANVIENDao(IDbConnection con)
        {
            _dbConnection = con;
            InitSqlQuery();
        }

        #endregion


        private void ToLowerQuery()
        {
            _SQLDelete = _SQLDelete.ToLower();
            _SQLUpdate = _SQLUpdate.ToLower();
            _SQLInsert = _SQLInsert.ToLower();
            _SQLSelectByKey = _SQLSelectByKey.ToLower();
        }

        private void ToUpperQuery()
        {
            _SQLDelete = _SQLDelete.ToUpper();
            _SQLUpdate = _SQLUpdate.ToUpper();
            _SQLInsert = _SQLInsert.ToUpper();
            _SQLSelectByKey = _SQLSelectByKey.ToUpper();
        }

        #region InitSqlQuery
        private void InitSqlQuery()
       {
            string query;

            query = @"SELECT MANHANVIEN, MALOAI, TENNHANVIEN, CHUCVU, NGAYSINH, GIOITINH, CMND, DIACHI, QUEQUAN, DIDONG, LUONGCOBAN, EMAIL, THEODOI, NGAYTAO, NGUOITAO, NGAYCAPNHAT, NGUOICAPNHAT, ISDELETE FROM NHANVIEN WHERE (MANHANVIEN = @MANHANVIEN)";
	        _SQLSelectByKey = query;

            query = @"UPDATE NHANVIEN SET MALOAI = @MALOAI, TENNHANVIEN = @TENNHANVIEN, CHUCVU = @CHUCVU, NGAYSINH = @NGAYSINH, GIOITINH = @GIOITINH, CMND = @CMND, DIACHI = @DIACHI, QUEQUAN = @QUEQUAN, DIDONG = @DIDONG, LUONGCOBAN = @LUONGCOBAN, EMAIL = @EMAIL, THEODOI = @THEODOI, NGAYTAO = @NGAYTAO, NGUOITAO = @NGUOITAO, NGAYCAPNHAT = @NGAYCAPNHAT, NGUOICAPNHAT = @NGUOICAPNHAT, ISDELETE = @ISDELETE WHERE (MANHANVIEN = @MANHANVIEN)";
	        _SQLUpdate = query;

            query = @"INSERT INTO NHANVIEN VALUES (@MALOAI, @TENNHANVIEN, @CHUCVU, @NGAYSINH, @GIOITINH, @CMND, @DIACHI, @QUEQUAN, @DIDONG, @LUONGCOBAN, @EMAIL, @THEODOI, @NGAYTAO, @NGUOITAO, @NGAYCAPNHAT, @NGUOICAPNHAT, @ISDELETE)";
	        _SQLInsert = query;

            query = @"DELETE FROM NHANVIEN WHERE (MANHANVIEN = @MANHANVIEN)";
	        _SQLDelete = query;

            query = @"SELECT MANHANVIEN, MALOAI, TENNHANVIEN, CHUCVU, NGAYSINH, GIOITINH, CMND, DIACHI, QUEQUAN, DIDONG, LUONGCOBAN, EMAIL, THEODOI, NGAYTAO, NGUOITAO, NGAYCAPNHAT, NGUOICAPNHAT, ISDELETE FROM NHANVIEN";
	        _SQLSelectAll = query;
        }
        #endregion
        public void LoadByCommandText(string commandtext, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = commandtext;

				if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        #region Save & Delete
        private void Save(NHANVIEN obj, IDbTransaction trans, bool isExisting)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command = _dbConnection.CreateCommand();

                if (!isExisting) { command.CommandText = _SQLInsert + ";"; }
                else { command.CommandText = _SQLUpdate + ";"; }

                         DalTools.addDbDataParameter(command, "MANHANVIEN", obj.MANHANVIEN, DbType.Int32);
                         DalTools.addDbDataParameter(command, "MALOAI", obj.MALOAI, DbType.Int32);
         DalTools.addDbDataParameter(command, "TENNHANVIEN", obj.TENNHANVIEN, DbType.String);
         DalTools.addDbDataParameter(command, "CHUCVU", obj.CHUCVU, DbType.String);
         DalTools.addDbDataParameter(command, "NGAYSINH", obj.NGAYSINH, DbType.DateTime);
         DalTools.addDbDataParameter(command, "GIOITINH", obj.GIOITINH, DbType.String);
         DalTools.addDbDataParameter(command, "CMND", obj.CMND, DbType.String);
         DalTools.addDbDataParameter(command, "DIACHI", obj.DIACHI, DbType.String);
         DalTools.addDbDataParameter(command, "QUEQUAN", obj.QUEQUAN, DbType.String);
         DalTools.addDbDataParameter(command, "DIDONG", obj.DIDONG, DbType.String);
         DalTools.addDbDataParameter(command, "LUONGCOBAN", obj.LUONGCOBAN, DbType.Decimal);
         DalTools.addDbDataParameter(command, "EMAIL", obj.EMAIL, DbType.String);
         DalTools.addDbDataParameter(command, "THEODOI", obj.THEODOI, DbType.Boolean);
         DalTools.addDbDataParameter(command, "NGAYTAO", obj.NGAYTAO, DbType.DateTime);
         DalTools.addDbDataParameter(command, "NGUOITAO", obj.NGUOITAO, DbType.String);
         DalTools.addDbDataParameter(command, "NGAYCAPNHAT", obj.NGAYCAPNHAT, DbType.DateTime);
         DalTools.addDbDataParameter(command, "NGUOICAPNHAT", obj.NGUOICAPNHAT, DbType.String);
         DalTools.addDbDataParameter(command, "ISDELETE", obj.ISDELETE, DbType.Boolean);

		        if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Delete(NHANVIEN obj, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                //throw new DalException("Connection has not been set.");
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = _SQLDelete + ";";

                         DalTools.addDbDataParameter(command, "MANHANVIEN", obj.MANHANVIEN, DbType.Int32);

		        if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Insert(NHANVIEN obj, IDbTransaction trans)
        {
            Save(obj, trans, false);
        }
        public void Update(NHANVIEN obj, IDbTransaction trans)
        {
            Save(obj, trans, true);
        }
        #endregion

        #region Command Execute & Read Properties
     
        private NHANVIENList SelectListByCommand(IDbCommand command)
        {
            NHANVIENList list = new NHANVIENList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {	
				NHANVIEN obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read()) 
					{ 
						obj = new NHANVIEN();
						ReadProperties(obj, reader);
						list.Add(obj); 
					}
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
		
        private NHANVIENList SelectListByCommand(IDbCommand command, IDbTransaction trans)
        {
            NHANVIENList list = new NHANVIENList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {	
				NHANVIEN obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
				command.Transaction = trans;
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read()) 
					{ 
						obj = new NHANVIEN();
						ReadProperties(obj, reader);
						list.Add(obj); 
					}
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private void DeleteListByCommand(IDbCommand command, IDbTransaction trans)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                if (trans != null) { command.Transaction = trans; }
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
	
        private void ReadProperties(NHANVIEN obj, IDataReader reader)
        {
            try
            {
                                if(!(reader["MANHANVIEN"] is DBNull))
		{
			obj.MANHANVIEN = (Int32)reader["MANHANVIEN"];
		}
                if(!(reader["MALOAI"] is DBNull))
		{
			obj.MALOAI = (Int32)reader["MALOAI"];
		}
                if(!(reader["TENNHANVIEN"] is DBNull))
		{
			obj.TENNHANVIEN = (string)reader["TENNHANVIEN"];
		}
                if(!(reader["CHUCVU"] is DBNull))
		{
			obj.CHUCVU = (string)reader["CHUCVU"];
		}
                if(!(reader["NGAYSINH"] is DBNull))
		{
			obj.NGAYSINH = (DateTime)reader["NGAYSINH"];
		}
                if(!(reader["GIOITINH"] is DBNull))
		{
			obj.GIOITINH = (string)reader["GIOITINH"];
		}
                if(!(reader["CMND"] is DBNull))
		{
			obj.CMND = (string)reader["CMND"];
		}
                if(!(reader["DIACHI"] is DBNull))
		{
			obj.DIACHI = (string)reader["DIACHI"];
		}
                if(!(reader["QUEQUAN"] is DBNull))
		{
			obj.QUEQUAN = (string)reader["QUEQUAN"];
		}
                if(!(reader["DIDONG"] is DBNull))
		{
			obj.DIDONG = (string)reader["DIDONG"];
		}
                if(!(reader["LUONGCOBAN"] is DBNull))
		{
			obj.LUONGCOBAN = (decimal)reader["LUONGCOBAN"];
		}
                if(!(reader["EMAIL"] is DBNull))
		{
			obj.EMAIL = (string)reader["EMAIL"];
		}
                if(!(reader["THEODOI"] is DBNull))
		{
			obj.THEODOI = (bool)reader["THEODOI"];
		}
                if(!(reader["NGAYTAO"] is DBNull))
		{
			obj.NGAYTAO = (DateTime)reader["NGAYTAO"];
		}
                if(!(reader["NGUOITAO"] is DBNull))
		{
			obj.NGUOITAO = (string)reader["NGUOITAO"];
		}
                if(!(reader["NGAYCAPNHAT"] is DBNull))
		{
			obj.NGAYCAPNHAT = (DateTime)reader["NGAYCAPNHAT"];
		}
                if(!(reader["NGUOICAPNHAT"] is DBNull))
		{
			obj.NGUOICAPNHAT = (string)reader["NGUOICAPNHAT"];
		}
                if(!(reader["ISDELETE"] is DBNull))
		{
			obj.ISDELETE = (bool)reader["ISDELETE"];
		}
            }
            catch (Exception ex)
            {
                throw new DalException("Failed to read properties from DataReader.", ex);
            }
        }
        #endregion
      
        public NHANVIEN SelectByKey(Int32 MANHANVIEN)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
	                 DalTools.addDbDataParameter(command, "MANHANVIEN", MANHANVIEN, DbType.Int32);
      	    NHANVIENList list =  SelectListByCommand(command);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public NHANVIEN SelectByKey(Int32 MANHANVIEN, IDbTransaction trans)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
	                 DalTools.addDbDataParameter(command, "MANHANVIEN", MANHANVIEN, DbType.Int32);
      	    NHANVIENList list =  SelectListByCommand(command, trans);
			
            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }
		
        public NHANVIENList SelectAll()
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectAll;
      	    NHANVIENList list =  SelectListByCommand(command);
	        return list;
        }

         public NHANVIENList SelectByCommand(string Command)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = Command;
      	    NHANVIENList list =  SelectListByCommand(command);
	        return list;
        }

        public NHANVIENList SelectByCondition(NHANVIENConditionForm condt)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM NHANVIEN " + GetCondtionQuery(condt) + ";";
            FillParamToCommand(command, condt);
            NHANVIENList list = SelectListByCommand(command);
            return list;
        }

        private void DeleteByCondition(NHANVIENConditionForm condt, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "DELETE FROM NHANVIEN " + GetCondtionQuery(condt) + ";";
            FillParamToCommand(command, condt);
            DeleteListByCommand(command, trans);
        }

        #region Make Condition Query & Fill Param from Condition to Command
        private string GetCondtionQuery(NHANVIENConditionForm obj)
        {
            if (obj == null) { return ""; }
            string s = "";
            	if (obj.IsSetMANHANVIEN)
	{
    	    s += " AND MANHANVIEN = @MANHANVIEN ";
	}
	if (obj.IsSetMALOAI)
	{
    	    s += " AND MALOAI = @MALOAI ";
	}
	if (obj.IsSetTENNHANVIEN)
	{
    	    s += " AND TENNHANVIEN = @TENNHANVIEN ";
	}
	if (obj.IsSetCHUCVU)
	{
    	    s += " AND CHUCVU = @CHUCVU ";
	}
	if (obj.IsSetNGAYSINH)
	{
    	    s += " AND NGAYSINH = @NGAYSINH ";
	}
	if (obj.IsSetGIOITINH)
	{
    	    s += " AND GIOITINH = @GIOITINH ";
	}
	if (obj.IsSetCMND)
	{
    	    s += " AND CMND = @CMND ";
	}
	if (obj.IsSetDIACHI)
	{
    	    s += " AND DIACHI = @DIACHI ";
	}
	if (obj.IsSetQUEQUAN)
	{
    	    s += " AND QUEQUAN = @QUEQUAN ";
	}
	if (obj.IsSetDIDONG)
	{
    	    s += " AND DIDONG = @DIDONG ";
	}
	if (obj.IsSetLUONGCOBAN)
	{
    	    s += " AND LUONGCOBAN = @LUONGCOBAN ";
	}
	if (obj.IsSetEMAIL)
	{
    	    s += " AND EMAIL = @EMAIL ";
	}
	if (obj.IsSetTHEODOI)
	{
    	    s += " AND THEODOI = @THEODOI ";
	}
	if (obj.IsSetNGAYTAO)
	{
    	    s += " AND NGAYTAO = @NGAYTAO ";
	}
	if (obj.IsSetNGUOITAO)
	{
    	    s += " AND NGUOITAO = @NGUOITAO ";
	}
	if (obj.IsSetNGAYCAPNHAT)
	{
    	    s += " AND NGAYCAPNHAT = @NGAYCAPNHAT ";
	}
	if (obj.IsSetNGUOICAPNHAT)
	{
    	    s += " AND NGUOICAPNHAT = @NGUOICAPNHAT ";
	}
	if (obj.IsSetISDELETE)
	{
    	    s += " AND ISDELETE = @ISDELETE ";
	}
            if (s.Length > 0)
            {
                s =" WHERE "+s.Substring(4);
                return s;
            }
            return "";
        }

        private void FillParamToCommand(IDbCommand command, NHANVIENConditionForm obj)
        {
	    if (obj == null) { return;}
            	            if (obj.IsSetMANHANVIEN)
            {
            	DalTools.addDbDataParameter(command, "MANHANVIEN", obj.MANHANVIEN, DbType.Int32); 
            }
            if (obj.IsSetMALOAI)
            {
            	DalTools.addDbDataParameter(command, "MALOAI", obj.MALOAI, DbType.Int32); 
            }
            if (obj.IsSetTENNHANVIEN)
            {
            	DalTools.addDbDataParameter(command, "TENNHANVIEN", obj.TENNHANVIEN, DbType.String); 
            }
            if (obj.IsSetCHUCVU)
            {
            	DalTools.addDbDataParameter(command, "CHUCVU", obj.CHUCVU, DbType.String); 
            }
            if (obj.IsSetNGAYSINH)
            {
            	DalTools.addDbDataParameter(command, "NGAYSINH", obj.NGAYSINH, DbType.DateTime); 
            }
            if (obj.IsSetGIOITINH)
            {
            	DalTools.addDbDataParameter(command, "GIOITINH", obj.GIOITINH, DbType.String); 
            }
            if (obj.IsSetCMND)
            {
            	DalTools.addDbDataParameter(command, "CMND", obj.CMND, DbType.String); 
            }
            if (obj.IsSetDIACHI)
            {
            	DalTools.addDbDataParameter(command, "DIACHI", obj.DIACHI, DbType.String); 
            }
            if (obj.IsSetQUEQUAN)
            {
            	DalTools.addDbDataParameter(command, "QUEQUAN", obj.QUEQUAN, DbType.String); 
            }
            if (obj.IsSetDIDONG)
            {
            	DalTools.addDbDataParameter(command, "DIDONG", obj.DIDONG, DbType.String); 
            }
            if (obj.IsSetLUONGCOBAN)
            {
            	DalTools.addDbDataParameter(command, "LUONGCOBAN", obj.LUONGCOBAN, DbType.Decimal); 
            }
            if (obj.IsSetEMAIL)
            {
            	DalTools.addDbDataParameter(command, "EMAIL", obj.EMAIL, DbType.String); 
            }
            if (obj.IsSetTHEODOI)
            {
            	DalTools.addDbDataParameter(command, "THEODOI", obj.THEODOI, DbType.Boolean); 
            }
            if (obj.IsSetNGAYTAO)
            {
            	DalTools.addDbDataParameter(command, "NGAYTAO", obj.NGAYTAO, DbType.DateTime); 
            }
            if (obj.IsSetNGUOITAO)
            {
            	DalTools.addDbDataParameter(command, "NGUOITAO", obj.NGUOITAO, DbType.String); 
            }
            if (obj.IsSetNGAYCAPNHAT)
            {
            	DalTools.addDbDataParameter(command, "NGAYCAPNHAT", obj.NGAYCAPNHAT, DbType.DateTime); 
            }
            if (obj.IsSetNGUOICAPNHAT)
            {
            	DalTools.addDbDataParameter(command, "NGUOICAPNHAT", obj.NGUOICAPNHAT, DbType.String); 
            }
            if (obj.IsSetISDELETE)
            {
            	DalTools.addDbDataParameter(command, "ISDELETE", obj.ISDELETE, DbType.Boolean); 
            }
        }
        #endregion
    }
}